/**
 * RecipeDAO.java
 * Nov 13, 2013
 * Copyright: phongtdse60732@fpt.edu.vn
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DBUltilities.ConnectDB;
import DTO.CookingDTO;
import DTO.DishDTO;
import DTO.RecipeDTO;

/**
 * @author trandophong
 * @version 1.0
 */
public class RecipeDAO {
	public void get(RecipeDTO recipe) {
		if (recipe.getId() != 0) {
			Connection conn = null;
			PreparedStatement preparedStatement = null;

			String sqlStatement = "SELECT * FROM RECIPE WHERE ID = ? ";

			try {
				conn = ConnectDB.getConnection();
				preparedStatement = conn.prepareStatement(sqlStatement);
				preparedStatement.setInt(1, recipe.getId());
				ResultSet rs = preparedStatement.executeQuery();

				while (rs.next()) {
					recipe.setId((Integer) rs.getObject(1));
					recipe.setName(new String(rs.getObject(2) == null ? "" : rs
							.getObject(2).toString()));
				}
			} catch (SQLException e) {
			} finally {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (SQLException e) {
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
			}
		}
	}

	public void add(RecipeDTO rc) {

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "INSERT INTO RECIPE (NAME) VALUES" + "(?)";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, rc.getName());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void update(RecipeDTO rc) {

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "UPDATE RECIPE SET NAME = ? WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, rc.getName());
			preparedStatement.setInt(2, rc.getId());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void delete(int id) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM RECIPE WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, id);

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public ArrayList<RecipeDTO> select() {
		Connection conn = null;
		Statement statement = null;
		ArrayList<RecipeDTO> list = new ArrayList<RecipeDTO>();

		String sqlStatement = "SELECT * FROM RECIPE";
		try {
			conn = ConnectDB.getConnection();
			statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlStatement);

			int id;
			String name;
			while (rs.next()) {
				id = (Integer) rs.getObject(1);
				name = new String(rs.getObject(2) == null ? "" : rs
						.getObject(2).toString());

				RecipeDTO recipeItem = new RecipeDTO(id, name);
				list.add(recipeItem);
			}
		} catch (Exception e) {
			return null;
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		return list;
	}

	public void getDishList(RecipeDTO recipe) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ArrayList<DishDTO> list = new ArrayList<DishDTO>();

		String sqlStatement = "SELECT DISH.* FROM DISH, RECIPE_DISH WHERE RECIPEID = ? AND DISHID = ID";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);
			preparedStatement.setInt(1, recipe.getId());
			ResultSet rs = preparedStatement.executeQuery();

			int id;
			String name;
			int calo;
			int cookingId;
			CookingDTO cooking;
			String description;
			String imgPath;

			while (rs.next()) {
				id = (Integer) rs.getObject(1);
				name = new String(rs.getObject(2) == null ? "" : rs
						.getObject(2).toString());
				calo = Integer.parseInt(rs.getObject(3) == null ? "0" : rs
						.getObject(3).toString());
				cookingId = Integer.parseInt(rs.getObject(4) == null ? "0" : rs
						.getObject(4).toString());
				cooking = new CookingDTO();
				cooking.setId(cookingId);
				description = new String(rs.getObject(5) == null ? "" : rs
						.getObject(5).toString());
				imgPath = new String(rs.getObject(6) == null ? "" : rs
						.getObject(6).toString());

				DishDTO dishItem = new DishDTO(id, name, calo, cooking,
						description, imgPath);
				list.add(dishItem);
			}

			recipe.setDishList(list);
		} catch (Exception e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void addRecipeDish(ArrayList<Integer> dishList, int recipeId) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		conn = ConnectDB.getConnection();

		// delete Dish in Recipe
		String sqlStatement = "DELETE FROM RECIPE_DISH WHERE RECIPEID=?";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, recipeId);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// add new Dish into Recipe
		sqlStatement = "INSERT INTO RECIPE_DISH (RECIPEID, DISHID) VALUES"
				+ "(?, ?)";

		try {
			preparedStatement = conn.prepareStatement(sqlStatement);
			conn.setAutoCommit(false);

			for (Integer dishId : dishList) {
				preparedStatement.setInt(1, recipeId);
				preparedStatement.setInt(2, dishId);
				preparedStatement.addBatch();
			}
			preparedStatement.executeBatch();

			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}
}
